package com.datalayer.utils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: Georgiana.Scarlat
 * Date: 7/2/12
 * Time: 4:25 PM
 */
public class SQLCommands {


    private Connection myConnection;


    public SQLCommands(Connection myConnection) {

        this.myConnection = myConnection;

    }

    public void deleteTable(String name) throws SQLException {

        String comm;
        Statement myStatement = null;

        comm = "DROP TABLE IF EXISTS " + name;

        try {

            myStatement = myConnection.createStatement();
            if (myStatement == null)
                throw new SQLException("Can't creare Statement");
            myStatement.executeUpdate(comm);

        } finally {
            if (myStatement != null) myStatement.close();
        }


    }

    public void createTable(String tableName, List<Column> columns, String aditional) throws SQLException {

        Statement myStatement = null;

        String comm = "CREATE TABLE " + tableName + "( ";

        for (Column c : columns) {
            comm += c.toString() + ", ";
        }

        if (aditional.equals("")) {
            comm = comm.substring(0, comm.length() - 2);
        } else {
            comm += aditional;
        }

        comm += ")";

        try {

            myStatement = myConnection.createStatement();
            if (myStatement == null)
                throw new SQLException("Can't creare Statement");
            myStatement.executeUpdate(comm);

        } finally {
            if (myStatement != null) myStatement.close();
        }


    }

    public void insertTable(String tableName, HashMap<String, String> values) throws SQLException {

        Statement myStatement = null;

        String comm = "INSERT INTO " + tableName + "( ";
        for (String type : values.keySet()) {
            comm += type + ", ";
        }

        comm = comm.substring(0, comm.length() - 2) + ") VALUES( ";
        for (String value : values.values()) {
            comm += value + ", ";
        }
        comm = comm.substring(0, comm.length() - 2) + ")";

        try {

            myStatement = myConnection.createStatement();
            if (myStatement == null)
                throw new SQLException("Can't creare Statement");
            myStatement.executeUpdate(comm);

        } finally {
            if (myStatement != null) myStatement.close();
        }
    }

    public void deleteFromTable(String tableName, List<String> conditions) throws SQLException {

        Statement myStatement = null;
        String condition = joinConditions(conditions);
        String comm = "delete from " + tableName + " where " + condition;

        try {

            myStatement = myConnection.createStatement();
            if (myStatement == null)
                throw new SQLException("Can't creare Statement");
            myStatement.executeUpdate(comm);

        } finally {
            if (myStatement != null) myStatement.close();
        }

    }

    private String joinConditions(List<String> conditions) {
        String condition = "";

        if (conditions.size() == 0) {
            return condition;
        }

        for (String cond : conditions) {
            condition += cond + " and ";
        }

        condition = condition.substring(0, condition.length() - 5);
        return condition;
    }

}
